﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Fadd.Data.Mappings;

namespace Fadd.Data
{
    /// <summary>
    /// Base class for database handling
    /// </summary>
    public abstract class DataLayerImp
    {
        private readonly StatementCompiler _compiler;
        private readonly IConnectionHelper _connectionHelper;
        private readonly Provider _providers;

        /// <summary>
        /// Initializes a new instance of the <see cref="DataLayer"/> class.
        /// </summary>
        /// <param name="connectionHelper">The connection helper.</param>
        protected DataLayerImp(IConnectionHelper connectionHelper)
        {
            Check.Require(connectionHelper, "connectionHelper");

            _connectionHelper = connectionHelper;
            _providers = new Provider();
            _compiler = new StatementCompiler(_providers, true);
        }

		/// <summary>
		/// Initializes a new instance of the <see cref="DataLayer"/> class.
		/// </summary>
		/// <param name="dataLayerImp">Another implementation.</param>
		protected DataLayerImp(DataLayerImp dataLayerImp)
		{
			Check.Require(dataLayerImp, "dataLayerImp");

			_connectionHelper = dataLayerImp._connectionHelper;
			_providers = dataLayerImp._providers;
			_compiler = dataLayerImp._compiler;
		}

		/// <summary>
		/// Determines if we are a transaction.
		/// </summary>
		public abstract bool IsTransaction { get; }

        /// <summary>
        /// Gets class that takes care of all providers.
        /// </summary>
        public Provider MappingProviders
        {
            get { return _providers; }
        }

        /// <summary>
        /// 
        /// </summary>
        public IConnectionHelper ConnectionHelper
        {
            get { return _connectionHelper; }
        }

        /// <summary>
        /// Get a single object.
        /// </summary>
        /// <typeparam name="T">Type of object to fetch</typeparam>
        /// <param name="statement">complete SQL statement with class/properties instead of table/columns.</param>
        /// <param name="parameters">parameters used in the statement.</param>
        /// <returns>object if found; otherwise null</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public T Query<T>(string statement, params object[] parameters) where T : new()
        {
            Check.NotEmpty(statement, "statement");
            CompiledStatement compiledStatement = _compiler.GetOrCreate("Query|" + typeof(T).Name + "|" + statement, statement, parameters);
            return FetchRow<T>(compiledStatement, parameters);
        }

        /// <summary>
        /// Gets a list of items.
        /// </summary>
        /// <param name="statement">Statements to get</param>
        /// <param name="parameters">parameters used in the statement.</param>
        /// <returns>A list of items.</returns>
        public IList<T> QueryCollection<T>(string statement, params object[] parameters) where T :new()
        {
            Check.NotEmpty(statement, "statement");
            CompiledStatement compiledStatement = _compiler.GetOrCreate("QueryCollection|" + typeof(T) + "|" + statement, statement, parameters);
            return FetchRows<T>(compiledStatement, parameters);
        }
        /// <summary>
        /// Executes an statement in the database.
        /// </summary>
        /// <typeparam name="T">Type of object to fetch</typeparam>
        /// <param name="statement">complete SQL statement with class/properties instead of table/columns.</param>
        /// <param name="parameters">parameters used in the statement.</param>
        /// <returns>object if found; otherwise null</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public void Execute<T>(string statement, params object[] parameters)
        {
            Type type = typeof (T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate("Query|" + type.Name, statement, parameters);
            Execute(compiledStatement, parameters);
        }

        /// <summary>
        /// Get a single object.
        /// </summary>
        /// <typeparam name="T">Type of object to fetch.</typeparam>
        /// <param name="conditions">Conditions (corresponds to statements in the WHERE clause).</param>
        /// <param name="parameters">Parameters used in the where clause.</param>
        /// <returns>object if found; otherwise null.</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public T Get<T>(string conditions, params object[] parameters) where T : new()
        {
            Type type = typeof (T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + '|' + conditions,
                                                                        "SELECT * FROM " + type.Name + " WHERE " +
                                                                        conditions,
                                                                        parameters);
            return FetchRow<T>(compiledStatement, parameters);
        }

        /// <summary>
        /// Get a single object using its primary key
        /// </summary>
        /// <typeparam name="T">Type of object to fetch, must have a defined mapping.</typeparam>
        /// <param name="id">Primary key value</param>
        /// <returns>object if found; otherwise null.</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public T GetById<T>(object id) where T : new()
        {
            Type type = typeof (T);
            IMapping mapping = _providers.GetMapping(type.Name);
            PropertyMapping pk = mapping.GetPrimaryKey();
            if (pk == null)
                throw new MappingException(type, "GetById requires exactly ONE primary key to be defined.");

            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|GetById",
                                                                        "SELECT * FROM " + type.Name + " WHERE " +
                                                                        pk.ColumnName + " =  ?",
                                                                        id);
            return FetchRow<T>(compiledStatement, new [] {id});
        }

        private T FetchRow<T>(CompiledStatement statement, IList<object> parameters) where T : new()
        {
            IDbConnection connection = CreateConnection();
            try
            {
                IDbCommand command = CreateCommand(connection, statement.Sql, parameters);
                IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                if (!reader.Read())
                    return default(T);

                T value = new T();
                // no properties have been specified, fill all properties.
                if (statement.Properties == null)
                {
                    foreach (PropertyMapping mapping in statement.Mapping.Properties)
						SetPropertyValue(value, mapping.PropertyName, reader[mapping.ColumnName]);
                        
                }
                else // fill only those properties that have been specified.
                {
                    foreach (string propertyName in statement.Properties)
						SetPropertyValue(value, propertyName, reader[statement.Mapping.GetColumnName(propertyName)]);
                }

                return value;
            }
            finally
            {
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
            }
        }

        private IList<T> FetchRows<T>(CompiledStatement statement, IList<object> parameters) where T : new()
        {
            IDbConnection connection = CreateConnection();
            try
            {
                IDbCommand command = CreateCommand(connection, statement.Sql, parameters);
                command.Connection = connection;
                IDataReader reader = command.ExecuteReader(CommandBehavior.Default);
                List<T> items = new List<T>();

                // no properties have been specified, fill all properties.
                if (statement.Properties == null)
                {
                    while (reader.Read())
                    {
                        T value = new T();
                        foreach (PropertyMapping mapping in statement.Mapping.Properties)
							SetPropertyValue(value, mapping.PropertyName, reader[mapping.ColumnName]);
                        items.Add(value);
                    }
                }
                else // fill only those properties that have been specified.
                {
                    while (reader.Read())
                    {
                        T value = new T();
                        foreach (string propertyName in statement.Properties)
							SetPropertyValue(value, propertyName, reader[statement.Mapping.GetColumnName(propertyName)]);
                        items.Add(value);
                    }
                }

                return items;
            }
            finally
            {
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
            }
        }

		private IList<T> FetchRows<T>(IMapping mapping, string sql, IList<object> parameters) where T : new()
		{
			IDbConnection connection = CreateConnection();
			try
			{
				IDbCommand command = CreateCommand(connection, sql, parameters);
				command.Connection = connection;
				IDataReader reader = command.ExecuteReader(CommandBehavior.Default);
				List<T> items = new List<T>();

				while (reader.Read())
				{
					T value = new T();
					foreach (PropertyMapping propertyMapping in mapping.Properties)
						SetPropertyValue(value, propertyMapping.PropertyName, reader[propertyMapping.ColumnName]);
					items.Add(value);
				}

				return items;
			}
			finally
			{
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
		}

		/// <summary>
		/// Used to assign a property with a value
		/// </summary>
		/// <param name="instance">Object containing the property.</param>
		/// <param name="name">Name of the property.</param>
		/// <param name="value">Value to set.</param>
		protected virtual void SetPropertyValue(object instance, string name, object value)
		{
			Property.Set(instance, name, value is DBNull ? null : value);
		}

        /// <summary>
        /// Save or update an object in the database (1. Checks if the PK is empty. 2. Checks if the object exists in the db).
        /// </summary>
        /// <param name="value">Value to create/update.</param>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public void Save(object value)
        {
            Type type = value.GetType();

            if (Exists(value))
                Update(type, value);
            else
                Create(value);
        }

        /// <summary>
        /// Create a new object in the database.
        /// </summary>
        /// <param name="value">Object to create.</param>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public void Create(object value)
        {
            Type type = value.GetType();
            IMapping mapping = _providers.GetMapping(type.Name);

            IDbConnection connection = CreateConnection();
            string sql = null;
            try
            {
                IDbCommand cmd = CreateCommand(connection);
                PropertyMapping autoincrementField = null;
                sql = "INSERT INTO " + mapping.TableName + " (";
                string values = ") VALUES (";
                int index = 0;
                foreach (PropertyMapping property in mapping.Properties)
                {
                    object paramValue;
                    // a primary key which have a generator should be generated automatically.
                    if (property.IsPrimaryKey && !string.IsNullOrEmpty(property.Generator))
                    {
                        switch (property.Generator)
                        {
                            case "autoincrement":
                                if (autoincrementField != null)
                                    throw new MappingException(type, "Most databases do only support one auto increment field. Second field: " +
                                                               type.Name + "." + property.PropertyName);
                                autoincrementField = property;
                                continue;
							case "sequence":
                        		{
                        			IDbCommand generatorCommand = CreateCommand(connection);
                        			paramValue = ConnectionHelper.GetNextSequenceValue(generatorCommand, type.Name + "_id_seq");
                        			generatorCommand.Dispose();
                        		}
                        		break;
							default:
                        		{
                        			IDbCommand generatorCommand = CreateCommand(connection);
                        			paramValue = ConnectionHelper.GetNextSequenceValue(generatorCommand, property.Generator);
                        			generatorCommand.Dispose();
                        		}
                        		break;
                        }
                    }
                    else
                    {
						
                        paramValue = Property.Get(value, property.PropertyName);
                        if (paramValue == null)
                            continue;
                    }

                    sql += property.ColumnName + ", ";
                    values += "@param" + index + ", ";
                    CreateParameter(cmd, paramValue, index++);
                }

                sql = sql.Remove(sql.Length - 2) + values.Remove(values.Length - 2) + ")";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                if (autoincrementField != null)
                {
					IDbCommand generatorCommand = CreateCommand(connection);
					object pkValue = ConnectionHelper.GetAutoIncrementValue(generatorCommand, autoincrementField.ColumnName);
                    Property.Set(value, autoincrementField.PropertyName, pkValue);
					generatorCommand.Dispose();
                }
            }
            catch(DbException err)
            {
                throw CreateDataLayerException(sql, "Failed to create '" + type.Name + "': " + value, err);
            }
            finally
            {
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
        }

        /// <summary>
        /// Update an existing object.
        /// </summary>
        /// <param name="value">Object to update</param>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public void Update(object value)
        {
            Update(value.GetType(), value);
        }

        /// <summary>
        /// Update an existing object
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <exception cref="DataLayerException">If SQL query fails.</exception>
        private void Update(Type type, object value)
        {
            IMapping mapping = _providers.GetMapping(type.Name);

            IDbConnection connection = CreateConnection();
            IDbCommand cmd = CreateCommand(connection);

            string sql = "UPDATE " + mapping.TableName + " SET ";
            int index = 0;
            foreach (PropertyMapping property in mapping.Properties)
            {
                if (property.IsPrimaryKey)
                    continue;
                sql += property.ColumnName + " = @param" + index + ", ";
                CreateParameter(cmd, Property.Get(value, property.PropertyName), index);
                ++index;
            }
            sql = sql.Remove(sql.Length - 2) + " WHERE ";
            foreach (PropertyMapping key in mapping.PrimaryKeys)
            {
                sql += key.PropertyName + " = @param" + index + " AND ";
                CreateParameter(cmd, Property.Get(value, key.PropertyName), index);
                ++index;
            }
            sql = sql.Remove(sql.Length - 4);

            try
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            catch (DbException err)
            {
                throw CreateDataLayerException(sql, "Failed to update '" + type.Name + "': " + value, err);
            }
            finally
            {
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
        }

        /// <summary>
        /// Checks if an object exists by using it's primary key.
        /// </summary>
        /// <typeparam name="T">Type of object to check</typeparam>
        /// <param name="id">Primary key</param>
        /// <returns>true if object was found; otherwise false.</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public bool ExistsById<T>(object id)
        {
            Type type = typeof (T);
            IMapping mapping = _providers.GetMapping(type.Name);
            PropertyMapping pk = mapping.GetPrimaryKey();
            if (pk == null)
                throw new MappingException(type, "ExistsById requires exactly ONE primary key to be defined.");

            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|ExistsById",
                                                                        "SELECT * FROM " + type.Name + " WHERE " +
                                                                        pk.ColumnName + " =  ?",
                                                                        id);
            return Exists(compiledStatement, new [] {id});
        }

        /// <summary>
        /// Checks if an object exists by providing it.
        /// </summary>
        /// <param name="model">object to check</param>
        /// <returns>true if object is stored in the database; otherwise false.</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public bool Exists(object model)
        {
            Type type = model.GetType();

            CompiledStatement compiledStatement = _compiler.Get(type.Name + "|Exists");
            List<object> parameters = new List<object>();

            IMapping mapping = _providers.GetMapping(type.Name);
            if (compiledStatement == null)
            {
                string sql = "SELECT * FROM " + type.Name + " WHERE ";
                int currentKey = 1;
                foreach (PropertyMapping key in mapping.PrimaryKeys)
                {
                    sql += key.PropertyName + " = ?";
                    
                    // If multiple primary keys
                    if (currentKey < mapping.PrimaryKeys.Count)
                        sql += " AND ";
                    currentKey++;

                    parameters.Add(Property.Get(model, key.PropertyName));
                }

                compiledStatement = _compiler.Create(type.Name + "|Exists", sql);
            }
            else
            {
                foreach (PropertyMapping key in mapping.PrimaryKeys)
                    parameters.Add(Property.Get(model, key.PropertyName));
            }

            return Exists(compiledStatement, parameters);
        }

        /// <summary>
        /// Checks if an object exists in the database
        /// </summary>
        /// <typeparam name="T">Type of object to check.</typeparam>
        /// <param name="conditions">Conditions.</param>
        /// <param name="parameters">parameters used in the conditions.</param>
        /// <returns>true if object is stored in the database; otherwise false.</returns>
        /// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
        /// <exception cref="DataLayerException">If data layer fails.</exception>
        public bool Exists<T>(string conditions, params object[] parameters)
        {
            Type type = typeof (T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|Exists" + conditions,
                                                                        "SELECT * FROM " + type.Name + " WHERE " +
                                                                        conditions,
                                                                        parameters);
            return Exists(compiledStatement, parameters);
        }

        /// <exception cref="DataLayerException"><c>DataLayerException</c>.</exception>
        private bool Exists(CompiledStatement statement, IList<object> parameters)
        {
        	IDbConnection connection = CreateConnection();
            try
            {
                IDbCommand command = CreateCommand(connection, statement.Sql, parameters);
                command.Connection = connection;
                IDataReader reader = command.ExecuteReader();
                bool exits = reader.Read();
                reader.Dispose();
                return exits;
            }
            catch (DbException err)
            {
                throw CreateDataLayerException(statement,
                                               "Failed to check if an object of type '" +
                                               statement.Mapping.Type.Name +
                                               "' exists.", err);
            }
			finally
			{
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
		}

        /// <exception cref="DataLayerException"><c>DataLayerException</c>.</exception>
        private void Execute(CompiledStatement statement, IList<object> parameters)
        {
        	IDbConnection connection = CreateConnection();
            try
            {
                IDbCommand command = CreateCommand(connection, statement.Sql, parameters);
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
            catch (DbException err)
            {
                throw CreateDataLayerException(statement,
                                               "Failed to execute statement for type '" +
                                               statement.Mapping.Type.Name +
                                               "'.", err);
            }
            finally
            {
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
        }

        /// <exception cref="DataLayerException"><c>DataLayerException</c>.</exception>
        private void Execute(string sql, IList<object> parameters)
        {
        	IDbConnection connection = CreateConnection();
            try
            {
                IDbCommand cmd = CreateCommand(connection);
                cmd.CommandText = sql;
                if (parameters != null)
                    for (int i = 0; i < parameters.Count; ++i)
                        CreateParameter(cmd, parameters[i], i);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (DbException err)
            {
                throw CreateDataLayerException(sql, "Failed to execute statement.", err);
            }
			finally
			{
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
		}

        private static DataLayerException CreateDataLayerException(CompiledStatement statement, string errMsg, DbException innerException)
        {
            return new DataLayerException(statement.Sql, errMsg, innerException);            
        }
        private static DataLayerException CreateDataLayerException(string sql, string errMsg, DbException innerException)
        {
            return new DataLayerException(sql, errMsg, innerException);
        }

        //private void FillObject(IDataReader reader, object value)

        /// <summary>
        /// Creates a complete database command with the help of the compiled statement.
        /// </summary>
        /// <param name="connection">Used to create the command</param>
		/// <param name="sql">SQL to execute</param>
        /// <param name="parameters">parameters used in the statement</param>
        /// <returns>A filled command</returns>
        /// <exception cref="ArgumentException">Parameter count doesn't match argument count in the statement.</exception>
        private IDbCommand CreateCommand(IDbConnection connection, string sql,
                                                IList<object> parameters)
        {
            IDbCommand cmd = CreateCommand(connection);
            cmd.CommandText = sql;
            if (parameters != null)
                for (int i = 0; i < parameters.Count; ++i)
                    CreateParameter(cmd, parameters[i], i);

            return cmd;
        }

        private static void CreateParameter(IDbCommand cmd, object value, int index)
        {
            IDataParameter dbparam = cmd.CreateParameter();
            dbparam.ParameterName = "@param" + index;
            dbparam.Value = value ?? DBNull.Value;
            cmd.Parameters.Add(dbparam);
        }


        /// <summary>
        /// Retrieves a collection of items.
        /// </summary>
        /// <typeparam name="T">Type of item to get.</typeparam>
        /// <param name="conditions">Conditions to limit.</param>
        /// <param name="parameters">Parameters used in the condition.</param>
        /// <returns>A list of objects; or an empty list</returns>
        public IList<T> Find<T>(string conditions, params object[] parameters) where T : new()
        {
            Type type = typeof(T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|Find|" + conditions,
                                                                        "SELECT * FROM " + type.Name + " WHERE " +
                                                                        conditions,
                                                                        parameters);

            IList<object> temp = parameters;
            return FetchRows<T>(compiledStatement, temp);
        }

        /// <summary>
        /// Retrieves all items of a certain type.
        /// </summary>
        /// <typeparam name="T">Type of item to get.</typeparam>
        /// <returns>A list of objects; or an empty list</returns>
        public IList<T> Find<T>() where T : new()
        {
            Type type = typeof(T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|SELECTALL",
                                                                        "SELECT * FROM " + type.Name);
            return FetchRows<T>(compiledStatement, null);
        }

		/// <summary>
		/// Execute a custom SQL query (Object SQL can not be used yet).
		/// </summary>
		/// <typeparam name="T">type of items to get.</typeparam>
		/// <param name="query">SQL query.</param>
		/// <param name="parameters">Parameters used in the query.</param>
		/// <returns>A list of elements.</returns>
		public IList<T> FindBySQL<T>(string query, params object[] parameters) where T : new()
		{
			IMapping mapping = _providers.GetMapping(typeof(T).Name);
			return FetchRows<T>(mapping, query, parameters);
		}

        /// <summary>
        /// Gets all items defined by the <see cref="Statement"/>.
        /// </summary>
        /// <typeparam name="T">type of items to get.</typeparam>
        /// <param name="statement">Statement specifying which elements to get.</param>
        /// <returns>A list of elements.</returns>
        public IList<T> Find<T>(Statement statement) where T :new()
        {
            Type type = typeof(T);

            CompiledStatement compiledStatement = _compiler.Get(type.Name + "|GetStmt" + statement.GetHashCode());
            if (compiledStatement == null)
            {
                string sql = "SELECT * FROM " + type.Name;
                if (!string.IsNullOrEmpty(statement.Conditions))
                    sql += " WHERE " + statement.Conditions;
                if (statement.OrderByClause != null && statement.OrderByClause.Length > 0)
                {
                    string orderBy = " ORDER BY ";
                    foreach (string propertyName in statement.OrderByClause)
                        if (!string.IsNullOrEmpty(propertyName))
                            orderBy += propertyName + ", ";
                    if (orderBy != " ORDER BY ")
                        sql += orderBy.Remove(orderBy.Length - 2);
                }
                if (statement.GroupByClause != null && statement.GroupByClause.Length > 0)
                {
                    string groupBy = " GROUP BY ";
                    foreach (string propertyName in statement.GroupByClause)
                        if (!string.IsNullOrEmpty(propertyName))
                            groupBy += propertyName + ", ";
                    if (groupBy != " GROUP BY ")
                        sql += groupBy.Remove(groupBy.Length - 2);
                    
                }
                compiledStatement = _compiler.Create(type.Name + "|GetStmt" + statement.GetHashCode(), sql);
            }

			if (statement.PageSize > 0)
			{
				IMapping mapping = _providers.GetMapping(type.Name);
				string pagingSql = _connectionHelper.Paging(mapping, compiledStatement.Sql, statement.Page, statement.PageSize);
				compiledStatement = compiledStatement.CreateWrapper(pagingSql);
			}

            return FetchRows<T>(compiledStatement, statement.Arguments);

        }

        /// <summary>
        /// Removes an object by using it's id property.
        /// </summary>
        /// <typeparam name="T">Type of object to remove.</typeparam>
        /// <param name="id">Primary key value</param>
        /// <exception cref="MappingException">RemoveById requires exactly one primary key.</exception>
        public void RemoveById<T>(object id)
        {
            Type type = typeof(T);
            IMapping mapping = _providers.GetMapping(type.Name);
            if (mapping.PrimaryKeys.Count != 1)
                throw new MappingException(type, "RemoveById requires exactly one primary key.");

            string sql = "DELETE FROM " + mapping.TableName + " WHERE " + mapping.PrimaryKeys[0].ColumnName + " = @param0";
            Execute(sql, new[] { id });
        }

        /// <summary>
        /// Removes an object by using it's id property.
        /// </summary>
        /// <typeparam name="T">Type of object to remove.</typeparam>
        public void Remove<T>(string conditions, params object[] parameters)
        {
            Type type = typeof(T);
            CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|Remove|" + conditions,
                                                                        "DELETE FROM " + type.Name + " WHERE " +
                                                                        conditions,
                                                                        parameters);
            Execute(compiledStatement, parameters);
        }

		/// <summary>
		/// Count number of items.
		/// </summary>
		/// <typeparam name="T">Type of object to check.</typeparam>
		/// <param name="conditions">Conditions.</param>
		/// <param name="arguments">Arguments used in the conditions.</param>
		/// <returns>true if object is stored in the database; otherwise false.</returns>
		/// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
		/// <exception cref="DataLayerException">If data layer fails.</exception>
		public int Count<T>(string conditions, params object[] arguments)
		{
			Type type = typeof(T);
			CompiledStatement compiledStatement = _compiler.GetOrCreate(type.Name + "|Remove|" + conditions,
																		"SELECT COUNT(*) FROM " + type.Name + " WHERE " +
																		conditions,
																		arguments);
			            IDbConnection connection = CreateConnection();
						try
						{
							IDbCommand command = CreateCommand(connection, compiledStatement.Sql, arguments);
							command.Connection = connection;
							return (int)command.ExecuteScalar();
						}
						finally
						{
							if (!IsTransaction)
							{
								connection.Close();
								connection.Dispose();
							}
						}
		}

		/// <summary>
		/// Count number of items.
		/// </summary>
		/// <typeparam name="T">Type of object to check.</typeparam>
		/// <returns>true if object is stored in the database; otherwise false.</returns>
		/// <exception cref="MappingException">If the class/table mapping is incorrect.</exception>
		/// <exception cref="DataLayerException">If data layer fails.</exception>
		public int Count<T>()
		{

			Type type = typeof(T);
			IMapping mapping = _providers.GetMapping(type.Name);
			IDbConnection connection = CreateConnection();
			try
			{
				IDbCommand command = CreateCommand(connection, "SELECT count(*) FROM " + mapping.TableName, null);
				command.Connection = connection;
				return (int)command.ExecuteScalar();
			}
			finally
			{
				if (!IsTransaction)
				{
					connection.Close();
					connection.Dispose();
				}
			}
		}

        /// <summary>
        /// Creates a connection, we use this method instead of the one in
        /// the connection helper to be able to use transactions (with a single connection).
        /// </summary>
        /// <returns>A database connection.</returns>
        protected abstract IDbConnection CreateConnection();

        /// <summary>
        /// Create a command.
        /// </summary>
        /// <param name="connection">Connection used to create the command</param>
        /// <returns>A <see cref="IDbCommand"/></returns>
        protected abstract IDbCommand CreateCommand(IDbConnection connection);
    }
}
